学校の関係で MySQL を触る機会が増えてきたので, MySQL の C++ 向けライブラリを一度触っておこうという忘備録.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #ifndef INCLUDED_PLAYING_CPPCONN_SQL_EXECUTOR_HPP | |
| #define INCLUDED_PLAYING_CPPCONN_SQL_EXECUTOR_HPP | |
| #include <srook/config/feature.hpp> | |
| #include <srook/config/attribute.hpp> | |
| #include <srook/type_traits/is_range.hpp> | |
| #include <srook/type_traits/decay.hpp> | |
| #include <srook/type_traits/conjunction.hpp> | |
| #include <srook/type_traits/is_constructible.hpp> | |
| #include <srook/type_traits/iterator/is_forwarditerator.hpp> | |
| #include <srook/type_traits/is_invocable.hpp> | |
| #include <srook/string/string_view.hpp> | |
| #include <srook/functional/invoke.hpp> | |
| #include <srook/iterator/range_iterators/range_iterator.hpp> | |
| #include <srook/iterator/range_access/begin_end.hpp> | |
| #include <srook/utility/forward.hpp> | |
| #include <mysql_driver.h> | |
| #include <mysql_connection.h> | |
| #include <mysql_error.h> | |
| #include <cppconn/Statement.h> | |
| #include <cppconn/ResultSet.h> | |
| #include <iostream> | |
| #include <memory> | |
| SROOK_NESTED_NAMESPACE(playing, cppconn) { | |
| class sql_executor { | |
| public: | |
| typedef std::shared_ptr<sql::ResultSet> result_ptr_type; | |
| SROOK_FORCE_INLINE | |
| sql_executor( | |
| const char* host, | |
| const char* user, | |
| const char* passwd, | |
| const char* dbname, | |
| std::ostream& err = std::cerr, | |
| const char* charcode = "utf8", | |
| const char* collate = "utf8_general_ci") SROOK_NOEXCEPT_TRUE | |
| : dbname_(dbname), connection_{}, statement_{}, is_valid_(false), cerr_(err) | |
| { | |
| try { | |
| sql::mysql::MySQL_Driver* driver = sql::mysql::get_mysql_driver_instance(); | |
| connection_.reset(driver->connect(host, user, passwd)); | |
| statement_.reset(connection_->createStatement()); | |
| statement_->execute(std::string("CREATE DATABASE IF NOT EXISTS ") + dbname + | |
| " DEFAULT CHARACTER SET " + charcode + " COLLATE " + collate); | |
| statement_->execute(std::string("USE ") + dbname); | |
| is_valid_ = true; | |
| } catch (const sql::SQLException& e) { | |
| out_exception(e); | |
| } catch (const std::exception& e) { | |
| out_exception(e); | |
| } | |
| } | |
| SROOK_FORCE_INLINE bool do_mutation(const char* s) | |
| { | |
| return execute(s); | |
| } | |
| template <class SQLStatement, | |
| SROOK_REQUIRES(srook::is_constructible<std::string, SROOK_DEDUCED_TYPENAME srook::decay<SQLStatement>::type>::value)> | |
| SROOK_FORCE_INLINE bool do_mutation(SQLStatement&& s) | |
| { | |
| return execute(s.data()); | |
| } | |
| template <class Range, | |
| SROOK_REQUIRES( | |
| srook::conjunction< | |
| srook::is_range<SROOK_DEDUCED_TYPENAME srook::decay<Range>::type>, | |
| srook::is_constructible<std::string, SROOK_DEDUCED_TYPENAME srook::decay<Range>::type::value_type> | |
| >::value | |
| )> | |
| SROOK_FORCE_INLINE bool do_mutation(Range&& range) | |
| { | |
| for (auto&& val : srook::forward<Range>(range)) { | |
| if (!do_mutation(val)) break; | |
| } | |
| return is_valid(); | |
| } | |
| template <class ForwardIterator, | |
| SROOK_REQUIRES(srook::is_forwarditerator<SROOK_DEDUCED_TYPENAME srook::decay<ForwardIterator>::type>::value)> | |
| SROOK_FORCE_INLINE bool do_mutation(ForwardIterator&& first, ForwardIterator&& last) | |
| { | |
| return do_mutation(srook::range::iterator::make_range_iterator(srook::forward<ForwardIterator>(first), srook::forward<ForwardIterator>(last))); | |
| } | |
| template <class F, | |
| SROOK_REQUIRES(srook::is_invocable<SROOK_DEDUCED_TYPENAME srook::decay<F>::type, result_ptr_type>::value)> | |
| SROOK_FORCE_INLINE bool do_select(const char* s, F&& fn) | |
| { | |
| return select(s, srook::forward<F>(fn)); | |
| } | |
| template <class SQLStatement, class F, | |
| SROOK_REQUIRES( | |
| srook::conjunction< | |
| srook::is_constructible<std::string, SROOK_DEDUCED_TYPENAME srook::decay<SQLStatement>::type>, | |
| srook::is_invocable<SROOK_DEDUCED_TYPENAME srook::decay<F>::type, result_ptr_type> | |
| >::value | |
| )> | |
| SROOK_FORCE_INLINE bool do_select(SQLStatement&& s, F&& fn) | |
| { | |
| return select(s.data(), srook::forward<F>(fn)); | |
| } | |
| template <class Range, class F, | |
| SROOK_REQUIRES( | |
| srook::conjunction< | |
| srook::is_invocable<SROOK_DEDUCED_TYPENAME srook::decay<F>::type, result_ptr_type>, | |
| srook::is_range<SROOK_DEDUCED_TYPENAME srook::decay<Range>::type>, | |
| srook::is_constructible<std::string, SROOK_DEDUCED_TYPENAME srook::decay<Range>::type::value_type> | |
| >::value | |
| )> | |
| SROOK_FORCE_INLINE bool do_select(Range&& range, F&& fn) | |
| { | |
| for (auto&& val : srook::forward<Range>(range)) { | |
| if (!do_select(val, fn)) break; | |
| } | |
| return is_valid(); | |
| } | |
| SROOK_CONSTEXPR bool is_valid() const SROOK_NOEXCEPT_TRUE | |
| { | |
| return is_valid_; | |
| } | |
| private: | |
| SROOK_FORCE_INLINE bool execute(const char* s) | |
| { | |
| if (is_valid()) { | |
| try { | |
| statement_->execute(s); | |
| } catch (const sql::SQLException& e) { | |
| out_exception(e); | |
| } catch (const std::exception& e) { | |
| out_exception(e); | |
| } | |
| } | |
| return is_valid(); | |
| } | |
| template <class F> | |
| SROOK_FORCE_INLINE bool select(const char* s, F&& fn) | |
| { | |
| if (is_valid()) { | |
| try { | |
| for (result_ptr_type res(statement_->executeQuery(s)); res->next();) { | |
| srook::invoke(srook::forward<F>(fn), res); | |
| } | |
| } catch (const sql::SQLException& e) { | |
| out_exception(e); | |
| } catch (const std::exception& e) { | |
| out_exception(e); | |
| } | |
| } | |
| return is_valid(); | |
| } | |
| SROOK_FORCE_INLINE void out_exception(const sql::SQLException& e) | |
| { | |
| out_exception(static_cast<std::exception>(e)); | |
| cerr_ << "My SQL error code: " << e.getErrorCode() << '\n' << | |
| "SQLState: " << e.getSQLState() << '\n'; | |
| } | |
| SROOK_FORCE_INLINE void out_exception(const std::exception& e) | |
| { | |
| is_valid_ = false; | |
| cerr_ << e.what() << '\n'; | |
| } | |
| private: | |
| std::string dbname_; | |
| std::unique_ptr<sql::Connection> connection_; | |
| std::unique_ptr<sql::Statement> statement_; | |
| bool is_valid_; | |
| std::ostream& cerr_; | |
| }; | |
| } SROOK_NESTED_NAMESPACE_END(cppconn, playing) | |
| #endif |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #include <array> | |
| #include <iostream> | |
| #include <srook/scope/unique_resource.hpp> | |
| #include "sql_executor.hpp" | |
| int main() | |
| { | |
| playing::cppconn::sql_executor executor("tcp://127.0.0.1:3306", "test", "passwd", "test_db"); | |
| { | |
| auto commit = srook::scope::make_unique_resource(&executor, [](playing::cppconn::sql_executor* r) { | |
| r->do_mutation("COMMIT"); | |
| }); | |
| const std::array<std::string, 5> init {{ | |
| "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\"", | |
| "SET AUTOCOMMIT = 0", | |
| "START TRANSACTION", | |
| "SET time_zone = \"+00:00\"", | |
| "CREATE TABLE IF NOT EXISTS customers (" | |
| " customer_no varchar(16) NOT NULL COMMENT '顧客番号'," | |
| " name varchar(64) NOT NULL COMMENT '氏名'," | |
| " zipcode char(8) NOT NULL COMMENT '郵便番号'," | |
| " address varchar(64) NOT NULL COMMENT '住所'," | |
| " tel_no varchar(16) NOT NULL COMMENT '電話番号'" | |
| ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='顧客マスタ'", | |
| }}; | |
| std::string insert = "INSERT IGNORE INTO customers VALUES "; | |
| // These are dummy data that generated by faker (https://github.com/joke2k/faker). | |
| const std::array<std::string, 6> set_table {{ | |
| insert + "('C001', '青山 花子', '958-3626', '大分県横浜市瀬谷区台場12丁目23番18号 勝どきコーポ435', '090-3537-6380')", | |
| insert + "('C002', '廣川 翔太', '297-3630', '群馬県西多摩郡奥多摩町平須賀14丁目5番2号 コーポ台東850', '88-1940-6921')", | |
| insert + "('C003', '田辺 浩', '596-4390', '大分県いすみ市虎ノ門虎ノ門ヒルズ森タワー31丁目11番1号', '080-4923-6200')", | |
| insert + "('C004', '井上 知実', '903-5859', '岩手県北区箪笥町7丁目6番2号 高田馬場クレスト753', '090-1267-5646')", | |
| insert + "('C005', '浜田 明美', '644-0375', '北海道武蔵野市蟇沼41丁目6番4号 パレス南赤田445', '090-3897-3724')", | |
| "ALTER TABLE customers ADD PRIMARY KEY (customer_no)" | |
| }}; | |
| executor.do_mutation(init); | |
| executor.do_mutation(set_table); | |
| } | |
| executor.do_select("SELECT * FROM customers", [](playing::cppconn::sql_executor::result_ptr_type ptr) { | |
| do { | |
| std::cout << | |
| "|顧客番号| " << ptr->getString("customer_no") << | |
| "\t|氏名| " << ptr->getString("name") << | |
| "\t|郵便番号| " << ptr->getString("zipcode") << | |
| "\t|住所| " << ptr->getString("address") << | |
| "\t|電話番号| " << ptr->getString("tel_no") << std::endl; | |
| } while (ptr->next()); | |
| }); | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # Dependencies | |
| # - Connector/C++: https://dev.mysql.com/downloads/connector/cpp/8.0.html | |
| # - Boost C++ Libraries 1.67.0: https://sourceforge.net/projects/boost/files/boost/1.67.0/ | |
| # - Srook C++ Libraries: https://github.com/falgon/SrookCppLibraries | |
| CXX:=clang++ -stdlib=libc++ | |
| CXXFLAGS:=-std=c++11 | |
| CPPFLAGS:=-Wall -Wextra -pedantic -I/usr/local/include/ -I./ `pkg-config mysqlclient --cflags --libs` | |
| LDFLAGS:=-L/usr/local/opt/mysql-client/lib | |
| LDLIBS:=-lmysqlcppconn | |
| DFLAGS:=-DBUILD_STATIC=false\ | |
| -DCMAKE_BUILD_TYPE=Debug\ | |
| -DWITH_BOOST=/usr/local/Cellar/boost/1.67.0_1/include/\ | |
| -DMYSQL_CONFIG_EXECUTABLE=/usr/local/opt/mysql-client/bin/mysql_config | |
| SRC:=$(wildcard *.cpp) | |
| EXE:=$(SRC:.cpp=) | |
| all:$(EXE) | |
| $(EXE):$(SRC) | |
| $(CXX) $< $(LDFLAGS) $(LDLIBS) $(CPPFLAGS) $(CXXFLAGS) $(DFLAGS) -o $@ | |
| run:$(SRC) | |
| ./$(EXE) | |
| clean: | |
| @$(RM) $(EXE) |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| version: '3.2' | |
| services: | |
| mysql: | |
| container_name: docker-mysql | |
| image: mysql:5.7 | |
| restart: always | |
| volumes: | |
| - ./conf:/etc/mysql/conf.d | |
| - ./logs:/var/log/mysql | |
| - ./init:/docker-entrypoint-initdb.d | |
| environment: | |
| MYSQL_ROOT_PASSWORD: rootpasswd | |
| MYSQL_DATABASE: sample | |
| MYSQL_USER: test | |
| MYSQL_PASSWORD: passwd | |
| TZ: Asia/Tokyo | |
| ports: | |
| - 3306:3306 | |
| pma: | |
| container_name: docker-pma | |
| image: phpmyadmin/phpmyadmin:latest | |
| restart: always | |
| environment: | |
| PMA_USER: root | |
| PMA_PASSWORD: rootpasswd | |
| ports: | |
| - 8080:80 | |
| links: | |
| - mysql:db |
これを実行すると, 次のように, 実在しそうでしなさそうな, 妙に怪しい雰囲気1の一覧が出力される.
|顧客番号| C001 |氏名| 青山 花子 |郵便番号| 958-3626 |住所| 大分県横浜市瀬谷区台場12丁目23番18号 勝どきコーポ435 |電話番号| 090-3537-6380
|顧客番号| C002 |氏名| 廣川 翔太 |郵便番号| 297-3630 |住所| 群馬県西多摩郡奥多摩町平須賀14丁目5番2号 コーポ台東850 |電話番号| 88-1940-6921
|顧客番号| C003 |氏名| 田辺 浩 |郵便番号| 596-4390 |住所| 大分県いすみ市虎ノ門虎ノ門ヒルズ森タワー31丁目11番1号 |電話番号| 080-4923-6200
|顧客番号| C004 |氏名| 井上 知実 |郵便番号| 903-5859 |住所| 岩手県北区箪笥町7丁目6番2号 高田馬場クレスト753 |電話番号| 090-1267-5646
|顧客番号| C005 |氏名| 浜田 明美 |郵便番号| 644-0375 |住所| 北海道武蔵野市蟇沼41丁目6番4号 パレス南赤田445 |電話番号| 090-3897-3724
見てのとおり, docker-compose を使って MySQL コンテナを立てて, そこに要求を投げる.
内容は, データベースを新たに作成してテーブル, 値を追加して, 追加項目を全てSELECTするだけ2.
C++ には RAII があるので, コード中でもそうなのだがSET AUTOCOMMIT = 0,START TRANSACTIONのあとに,
COMMITをし忘れるなんてことを防げるのが良い.
一応 SQL 文が格納されている Range かイテレータを渡せば順次実行, 単一の SQL を渡せばもちろんそれを実行するようにしている.
実際に必要に迫られているわけではないのでなんとも言えないが, 現時点では,
それなりに気持ちよく書ける程度にラッピングできたような気がする3.
1 点この Connector/C++ に対する不満を申し上げるとすれば, C++11 までにしか未だ対応していない点だろう.
しかし久しぶりに C++ 書いた. なんだろうこの実家感は.